package com.zhq.dao.imp;

import com.zhq.bean.Courier;
import com.zhq.dao.BaseCourierDao;
import com.zhq.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author:钟豪强
 * @Description:
 * @Date Created in  2020-08-14 20:56
 * @Modified By:
 */
public class CourierDaoMysql implements BaseCourierDao {
    private static String SQL_CONSOLE = " SELECT COUNT(ID) DATA_SIZE, COUNT(TO_DAYS(CREATETIME) = TO_DAYS(NOW()) OR NULL) DATA_DAY FROM COURIER ";
    private static String SQL_FIND_ALL = " SELECT * FROM COURIER ";
    private static String SQL_FIND_BY_USERPHONE = " SELECT * FROM COURIER WHERE USERPHONE = ?";
    private static String SQL_FIND_BY_USERNAME = " SELECT * FROM COURIER WHERE USERNAME = ?";
    private static String SQL_LOGIN = " SELECT * FROM COURIER WHERE USERNAME = ? AND PASSWORD = ?";
    private static String SQL_FIND_LIMIT = " SELECT * FROM COURIER LIMIT ?,? ";
    private static String SQL_INSERT = " INSERT INTO COURIER (USERNAME,USERPHONE,USERNUMBER,PASSWORD,CREATETIME) VALUES (?,?,?,?,NOW()) ";
    private static String SQL_UPDATE = " UPDATE COURIER SET USERNAME = ?, USERPHONE = ?, USERNUMBER = ?, PASSWORD = ? WHERE ID = ? ";
    private static String SQL_UPDATE_LAST_LOGIN_TIME = " UPDATE COURIER SET LASTLOGINTIME = ? WHERE ID = ? ";
    private static String SQL_UPDATE_EXPRESS_NUMBER = " UPDATE COURIER SET EXPRESSNUMBER = ? WHERE ID = ? ";
    private static String SQL_DELETE = " DELETE FROM COURIER WHERE ID = ? ";
    /**
     * 用于查询数据库中快递员的数量(总数和新增)
     *
     * @return {size:总数,day:新增}
     */
    @Override
    public Map<String, Integer> console() {
        HashMap<String, Integer> map = new HashMap<>();
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            //3.填充参数(可选)
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取结果
            while (result.next()) {
                int data_size = result.getInt("data_size");
                int data_day = result.getInt("data_day");
                map.put("data_size",data_size);
                map.put("data_day",data_day);
            }
            return map;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 用于查询所有快递员信息
     *
     * @param limit      0代表分页(默认),1代表不分页
     * @param offset     表示SQL语句起始索引
     * @param pageNumber
     * @return
     */
    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Courier> data = new ArrayList<>();
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译
        try {
            if (limit) {
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                //3.填充参数(可选)
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取结果
            while (result.next()) {
                //int id, String username, String userPhone, String userNumber, String userWord, int expressNumber, Timestamp createTime, Timestamp lastLoginTime
                int id = result.getInt("id");
                String username = result.getString("username");
                String userPhone = result.getString("userphone");
                String userNumber = result.getString("usernumber");
                String password = result.getString("password");
                int expressNumber = result.getInt("expressnumber");
                Timestamp createTime = result.getTimestamp("createtime");
                Timestamp lastLoginTime = result.getTimestamp("lastlogintime");
                Courier courier= new Courier(id,username,userPhone,userNumber,password,expressNumber,createTime,lastLoginTime);
                data.add(courier);
            }
            return data;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 根据快递员手机号码查找快递员信息
     *
     * @param userPhone 快递员手机号码
     * @return 快递员信息
     */
    @Override
    public Courier findByUserPhone(String userPhone) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
            //3.填充参数
            state.setString(1,userPhone);
            //4.执行SQL语句,获取结果
            result = state.executeQuery();
            while (result.next()) {
                int id = result.getInt("id");
                String username = result.getString("username");
                String userNumber = result.getString("usernumber");
                String password = result.getString("password");
                int expressNumber = result.getInt("expressnumber");
                Timestamp createTime = result.getTimestamp("createtime");
                Timestamp lastLoginTime = result.getTimestamp("lastlogintime");
                Courier courier= new Courier(id,username,userPhone,userNumber,password,expressNumber,createTime,lastLoginTime);
                return courier;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 根据快递员姓名查找快递员信息
     *
     * @param username 快递员姓名
     * @return 快递员信息
     */
    @Override
    public Courier findByUserName(String username) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_FIND_BY_USERNAME);
            //3.填充参数
            state.setString(1,username);
            //4.执行SQL语句,获取结果
            result = state.executeQuery();
            while (result.next()) {
                int id = result.getInt("id");
                String userPhone = result.getString("userphone");
                String userNumber = result.getString("usernumber");
                String password = result.getString("password");
                int expressNumber = result.getInt("expressnumber");
                Timestamp createTime = result.getTimestamp("createtime");
                Timestamp lastLoginTime = result.getTimestamp("lastlogintime");
                Courier courier= new Courier(id,username,userPhone,userNumber,password,expressNumber,createTime,lastLoginTime);
                return courier;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 用于添加快递员信息
     *
     * @param username   快递员姓名
     * @param userPhone  快递员手机号
     * @param userNumber 快递员身份证
     * @param password   密码
     * @return true代表添加成功
     */
    @Override
    public boolean insert(String username, String userPhone, String userNumber, String password) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //3.填充参数
            state.setString(1,username);
            state.setString(2,userPhone);
            state.setString(3,userNumber);
            state.setString(4,password);
            //4.执行SQL语句,获取结果
            return state.executeUpdate() > 0 ? true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用于根据id修改快递员的派件总数
     *
     * @param id      快递员id
     * @param expressNumber 快递员派件个数
     * @return true代表修改成功
     */
    @Override
    public boolean updateExpressNumber(int id, int expressNumber) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_UPDATE_EXPRESS_NUMBER);
            //3.填充参数
            state.setInt(1,expressNumber);
            state.setInt(2,id);
            //4.执行SQL语句,获取结果
            return state.executeUpdate() > 0 ? true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用于根据id,修改快递员信息
     *
     * @param id            用户id
     * @param lastLoginTime 快递员上次在线时间
     * @return 返回true代表修改成功
     */
    @Override
    public boolean updateLastLoginTime(int id, Timestamp lastLoginTime) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_UPDATE_LAST_LOGIN_TIME);
            //3.填充参数
            state.setTimestamp(1,lastLoginTime);
            state.setInt(2,id);
            //4.执行SQL语句,获取结果
            return state.executeUpdate() > 0 ? true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用于根据id,修改快递员信息
     *
     * @param id         快递员id
     * @param newCourier 新的快递员信息
     * @return 返回true代表修改成功
     */
    @Override
    public boolean update(int id, Courier newCourier) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            //3.填充参数
            state.setString(1,newCourier.getUsername());
            state.setString(2,newCourier.getUserPhone());
            state.setString(3,newCourier.getUserNumber());
            state.setString(4,newCourier.getPassword());
            state.setInt(5,id);
            //4.执行SQL语句,获取结果
            return state.executeUpdate() > 0 ? true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用于根据id,删除快递员信息
     *
     * @param id 快递员id
     * @return true代表修改成功
     */
    @Override
    public boolean delete(int id) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_DELETE);
            //3.填充参数
            state.setInt(1,id);
            //4.执行SQL语句,获取结果
            return state.executeUpdate() > 0 ? true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 快递员登录
     *
     * @param username 快递员姓名
     * @param password 密码
     * @return 返回true代表登录成功
     */
    @Override
    public boolean login(String username, String password) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_LOGIN);
            //3.填充参数
            state.setString(1,username);
            state.setString(2,password);
            //4.执行SQL语句,获取结果
            ResultSet resultSet = state.executeQuery();
            return resultSet.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
